package archive;



import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/*
 * MySql facilitator
 * by Martin Robinson
 * */

public class MySqlDB implements Sql{
	private final String _user,
	                      _pass,
	                      _url;
	private Connection _con;
	/**
	 * constructor
	 * @param usr  as user name String
	 * @param pass as password String
	 * @param url  as url string
	 * @throws ClassNotFoundException
	 */
	public MySqlDB(final String usr, final String pass, final String url) throws ClassNotFoundException{
		Class.forName("com.mysql.jdbc.Driver");
		_user = usr;
		_pass = pass;
		_url = url;
	}

  public void connect() {
    try {
      _con = DriverManager.getConnection(_url,_user,_pass);
    } catch (SQLException ex) {
    }
  }

  public void disconnect() {
    try {
      _con.close();
      _con=null;
    } catch (SQLException ex) {
    }
  }
  
  public boolean isConnected(){
    return _con!=null;
  }
	
	/* (non-Javadoc)
	 * @see Lib.Sql#exec(java.lang.String)
	 */
	public boolean exec(final String sqlquery) throws SQLException{

		_con.createStatement().execute(sqlquery);

		return true;
	}
	
	/**
	 * private function
	 * wrap string of aligment
	 * @param size size to wrap
	 * @param c filling char
	 * @return string
	 */
	private String wrap(int size, char c){
		String tmp = "";
		for (int i=0;i<size;i++)
			tmp+= c;
		return tmp;
	}
	/* (non-Javadoc)
	 * @see Lib.Sql#view(java.lang.String)
	 */
	public String view(final String tablename) throws SQLException{
		Connection con = DriverManager.getConnection(_url,_user,_pass);

		ResultSet r = con.createStatement().executeQuery("SELECT * FROM " + tablename + ";");
		ResultSetMetaData name = r.getMetaData();
		
		String ret = "table : " + tablename + "\n",
			   tmp = new String();

		for (int i=1;i<=name.getColumnCount();i++){
			tmp += "|[" + name.getColumnName(i)+"]";
			tmp += wrap(16 - tmp.length()%16, ' ');
		}
		ret += tmp + "|\n";
		while(r.next()){
			tmp = new String();
			for (int i=1;i<=name.getColumnCount();i++){
				tmp += "|" + r.getString(i);
				tmp += wrap(16 - tmp.length()%16, ' ');
			}
			ret += tmp + "|\n";
		}
		
		con.close();
		return ret;
	}
	
	/* (non-Javadoc)
	 * @see Lib.Sql#query(java.lang.String)
	 */
	public List<Map> query(final String req) throws SQLException{
    
		ResultSet r = _con.createStatement().executeQuery(req);
		ResultSetMetaData name = r.getMetaData();
		
		List<Map> l = new LinkedList();
		
		while(r.next()){
			Map<String, Object> row = new HashMap();
			for (int i=1;i<=name.getColumnCount();i++)
				row.put(name.getColumnName(i), r.getObject(i));
			l.add(row);
		}

		return l;
	}
	
	public <T> T queryObject(final String req){
    try {
      ResultSet r = _con.createStatement().executeQuery(req);
      r.next();
      return (T)r.getObject(0);
    } catch (SQLException ex) {
    }

    return null;
	}
  
	/* (non-Javadoc)
	 * @see controler.Sql#queryOne(java.lang.String)
	 */
	public Map queryMap(final String req) throws SQLException{
		ResultSet r = _con.createStatement().executeQuery(req);
		ResultSetMetaData name = r.getMetaData();

		r.next();
		Map<String, Object> row = new HashMap();
		for (int i=1;i<=name.getColumnCount();i++)
			row.put(name.getColumnName(i), r.getObject(i));

		
		return row;
	}
  
	public List queryList(final String req) throws SQLException{
		ResultSet r = _con.createStatement().executeQuery(req);

		r.next();
		List<Object> col = new ArrayList();
    while(r.next())
			col.add(r.getObject(0));

		
		return col;
	}
	
	/* (non-Javadoc)
	 * @see controler.Sql#exist(java.lang.String)
	 */
	public boolean exist(final String req) throws SQLException{
		Connection con = DriverManager.getConnection(_url,_user,_pass);
		ResultSet r = con.createStatement().executeQuery("SELECT EXISTS("+req+")");
		r.next();
		boolean b = r.getInt(1) == 1; 
		con.close();
		return b;
	}

	/*test*/
	public static void main(String [] args) throws ClassNotFoundException, SQLException{
		Sql test = new MySqlDB("root","root", "jdbc:mysql://127.0.0.1/testbd");
		
		test.exec("create table Car(marque varchar(32),modele varchar(32),annee YEAR(2),  consommation float);");
		
		String qu = "INSERT INTO Car (marque, modele, annee, consommation) VALUES";
		qu += "('honda', 'Elantra', 50, 5.6),";
		qu += "('subaru', 'spider', 13, 8.0),";
		qu += "('pontiac', 'Impresa', 01, 4.0),";
		qu += "('Dodge', 'silverado', 85, 16.0),";
		qu += "('Volkswagen','smart', 05, 1.0);";
		
		test.exec(qu);
		
		System.out.println(test.view("Car"));
		
		for (Map<String, Object> m: test.query("SELECT * FROM Car")){
			System.out.println(m.get("marque"));
		}
		System.out.println();
		
		test.exec("DELETE FROM Car");
		test.exec("DROP TABLE Car");
	}
}
